create database Employees;
use Employees;

drop TABLE salary;
desc salary；
select *from salary;
show tables;
drop table employee_project_relation;
select *from employee_information;
desc project;
desc employee_information;


BEGIN;
--  将白晶晶修改为柳如烟,柳如烟修改为白晶晶
UPDATE employee_information set employee_name='柳如烟' WHERE employee_name='白晶晶';
UPDATE employee_information set employee_name='白晶晶' WHERE employee_name='柳如烟';
ROLLBACK;
COMMIT;


SELECT user, host, authentication_string FROM mysql.user;

-- 创建用户
-- create user 'user01' @'localhost' identified by 'user01';
create user 'user1'@'localhost'IDENTIFIED by '123456';
create user 'user2'@'localhost'IDENTIFIED by '123456';

-- 授权
-- GRANT SELECT, INSERT ON employee.* TO 'user01';
-- 授权
grant INSERT ON salary TO 'user1' @'localhost'; 
grant UPDATE ON project TO 'user1' @'localhost'; 
-- 验证
SHOW GRANTS FOR 'user1'@'localhost';

-- 回收
grant ALL ON project TO 'user2' @'localhost'; 
REVOKE INSERT ON project FROM 'user2' @'localhost';
-- 验证
SHOW GRANTS FOR 'user2'@'localhost';



-- 验证完整性
-- 验证实体完整性
select*from project; 
desc project;

insert into project VALUE('P001','测试项目','2002-10-14','2012-12-4');

-- 验证参照完整性
select*from department_position;
DESC department_position;
insert into department_position VALUE ('PS008','产品经理','D004');

-- 验证定义完整性
INSERT into project VALUE('P001','测试项目','2024-4-6','2005-10-24');



-- .表的主码不为空、不能重复的验证，用命令的方式。
desc project;
select *from project;
INSERT INTO project VALUE ('P001', '测试项目', '2024-01-01', '2024-11-30');
INSERT INTO project VALUE (NULL, '测试项目', '2024-01-01', '2024-11-30');
ALTER TABLE project ADD PRIMARY KEY (project_id);

DELETE from project WHERE project_id='NULL';



/*
插入，修改，删除
*/

-- 插入
-- 向工资表插入数据
INSERT INTO salary 
VALUES
    ('S026', 7000.00, 800.00, 300.00, 200.00,100.00);
    
    
--   向员工表插入数据
INSERT INTO employee_information 
VALUES
    ('E026', '白骨精', '女', '335445682232054', '1778588888', '20215545@qq.com', 'D003','PS006','S026','P003');


-- 修改数据
-- 将员工‘杀生’修改为‘沙僧’
UPDATE employee_information set employee_name='沙僧' where employee_name='杀生';
select *from employee_information;


-- 将财务部经理修改为柳如烟
UPDATE company_department set manager_id=(select employee_number from employee_information where employee_name='柳如烟') where department_name='财务部';

-- 将基本工资工资小于5000的员工，基本工资修改为5000
UPDATE salary set basic_salary=5000.00 where basic_salary<5000.00;

-- 将补贴小于300的员工，补贴修改为300
UPDATE salary set subsidy=300.00 where subsidy<300.00;

-- 将白晶晶修改为市场部
UPDATE employee_information set department_number=(SELECT department_number from company_department where department_name='市场部') where employee_name='白晶晶';

-- 将黑旋风修改为李逵
UPDATE employee_information set employee_name='李逵' where employee_name='黑旋风';
-- 将浪里白条修改为张顺
UPDATE employee_information set employee_name='张顺' where employee_name='浪里白条';

-- 将智慧家项目的开始时间修改为2022-10-24
UPDATE project set start_date='2022-10-24' where project_name='智慧家';

-- 将张三修改为陈平安
UPDATE employee_information set employee_name='陈平安' where employee_name='张三';
select *from employee_information;

select *from project;





/*
查询
*/

-- 单表查询
-- 查询员工表
select employee_number '编号',employee_name '姓名',gender '性别',id_card_number '身份证号',mobile_phone '电话',email '邮箱' from employee_information;
-- 查询工资表
select salary_id '工资编号', basic_salary '基本工资',five_insurances_and_one_fund '五险一金',performance_bonus '绩效奖金',subsidy '补贴',
    overtime_pay '加班费' from salary;
-- 查询项目表
select project_id '项目编号',project_name '项目名称',start_date'开始时间',end_date '结束时间' from project;
-- 查询部门表
select    department_number '部门编号', department_name '部门名称',manager_id '负责人编号' from company_department;
-- 查询职位表
select position_number '职位编号',position_name '职位名称',department_number '部门编号' from department_position;
-- 查询姓张的员工信息
select employee_number '编号',employee_name '姓名',gender '性别',id_card_number '身份证号',mobile_phone '电话',email '邮箱' from employee_information where employee_name like '张%';
-- 查询研发部的员工信息
select employee_number '编号',employee_name '姓名',gender '性别',id_card_number '身份证号',mobile_phone '电话',email '邮箱' from employee_information where department_number='D001';
-- 查询所有的财务会计
select employee_number '编号',employee_name '姓名',gender '性别',id_card_number '身份证号',mobile_phone '电话',email '邮箱' from employee_information where position_number='PS007';
-- 查询所有参见‘智慧家’的员工
select employee_number '编号',employee_name '姓名',gender '性别',id_card_number '身份证号',mobile_phone '电话',email '邮箱' from employee_information where project_id='P001';
-- 查询性别为女的员工
select employee_number '编号',employee_name '姓名',gender '性别',id_card_number '身份证号',mobile_phone '电话',email '邮箱' from employee_information where gender='女';





-- 连接查询
-- 查询员工工资
select e.employee_name '姓名',e.gender '性别',s.basic_salary '基本工资',s.five_insurances_and_one_fund '五险一金',s.performance_bonus '绩效奖金',s.subsidy '补贴',s.overtime_pay '加班费',s.basic_salary+s.five_insurances_and_one_fund+s.performance_bonus+s.subsidy+s.overtime_pay '总工资' from employee_information e,salary s where e.salary_id=s.salary_id;
-- 查询员工职位
select e.employee_name '姓名',e.gender '性别',d.position_name '职位' from employee_information e,department_position d where e.position_number=d.position_number;
-- 查询员工部门
select e.employee_name '姓名',e.gender '性别',c.department_name '部门' from employee_information e,company_department c where c.department_number=e.department_number;
-- 查询各部门人数
select c.department_name '部门',COUNT(*)'人数' from employee_information e,company_department c where e.department_number=c.department_number GROUP BY c.department_name;
-- 查询各部门平均工资
select c.department_name '部门',avg(s.basic_salary+s.five_insurances_and_one_fund+s.performance_bonus+s.subsidy+s.overtime_pay)'平均工资' from  employee_information e,company_department c,salary s where e.salary_id=s.salary_id and e.department_number=c.department_number GROUP BY c.department_name;


-- 子查询（嵌套查询）
-- 查询工资高于9500的员工
select  e.employee_name '姓名' from employee_information e where e.salary_id in (select salary_id from salary s where s.basic_salary+s.five_insurances_and_one_fund+s.performance_bonus+s.subsidy+s.overtime_pay >=9500); 

-- 查询五险一金为1200的员工
select  e.employee_name '姓名' from employee_information e where e.salary_id in (select salary_id from salary s where s.five_insurances_and_one_fund =1200); 

-- 查询是财务总监的员工信息
select *from employee_information e where e.position_number in (select position_number from department_position where position_name ='财务总监');
-- 查询研发部的职位
select d.position_name '职位' from department_position d where d.department_number in (select department_number from company_department c where c.department_name='研发部');

-- 查询各部门负责人信息
select *from employee_information e where e.employee_number in (SELECT manager_id from company_department);
-- 查询财务的职位
select d.position_name '职位' from department_position d where d.department_number in (select department_number from company_department c where c.department_name='财务部');


-- 集合
-- 查询研发部的人和其他部门负责人信息
select *from employee_information where department_number='D001' UNION select *from employee_information e where e.employee_number in (SELECT manager_id from company_department);



  department_number VARCHAR(20),-- 部门编号
    position_number VARCHAR(20),-- 职位编号
    salary_id VARCHAR(20) NOT NULL,-- 工资编号
     project_id VARCHAR(20) ,-- 项目编号



desc company_department;
drop table company_department;
CREATE TABLE company_department (
    department_number VARCHAR(20) PRIMARY KEY,-- 部门编号
    department_name VARCHAR(100) NOT NULL,-- 部门名称
    manager_id VARCHAR(20)-- 负责人编号
);



/*
视图
*/


-- 员工工资表
create view employee_salary as select e.employee_name '姓名',e.gender '性别',s.basic_salary '基本工资',s.five_insurances_and_one_fund '五险一金',s.performance_bonus '绩效奖金',s.subsidy '补贴',s.overtime_pay '加班费',s.basic_salary+s.five_insurances_and_one_fund+s.performance_bonus+s.subsidy+s.overtime_pay '总工资' from employee_information e,salary s where e.salary_id=s.salary_id;
select*from employee_salary;


-- 员工部门表
create view employee_department as select e.employee_name '姓名',e.gender '性别',c.department_name '部门' from employee_information e,company_department c where e.department_number=c.department_number;
select*from employee_department;


-- 部门负责人
create view department_manager as select c.department_name '部门',e.employee_name '经理' from employee_information e,company_department c where e.employee_number=c.manager_id;
 show tables;
drop VIEW department_manager;
select *from department_manager;

-- 员工项目表
create view employee_project as select e.employee_name '姓名',p.project_name'项目名称',p.start_date '开始时间',p.end_date '结束时间' from employee_information e,employee_project_relation ep,project p where e.employee_number=ep.employee_number and p.project_id=ep.project_id;
 
 select *from employee_project;



/*
插入数据
*/
 desc  employee_project_relation;


-- 向工资表插入数据
INSERT INTO salary 
VALUES
    ('S001', 6000.00, 1200.00, 300.00, 50.00,100.00),
    ('S002', 7000.00, 1200.00, 700.00, 100.00,500.00),
    ('S003', 5000.00, 1000.00, 400.00, 80.00,600.00),
    ('S004', 5000.00, 1000.00, 300.00, 60.00,200.00),
    ('S005', 5000.00, 1000.00, 400.00, 40.00,600.00),
    ('S006', 6000.00, 1200.00, 900.00, 90.00,300.00),
    ('S007', 5000.00, 1000.00, 800.00, 100.00,400.00),
    ('S008', 4000.00, 1200.00, 500.00, 20.00,700.00),
    ('S009', 5000.00, 1000.00, 100.00, 60.00,300.00),
    ('S010', 9000.00, 1000.00, 200.00, 80.00,600.00),
    ('S011', 5000.00, 800.00, 300.00, 90.00,500.00),
    ('S013', 5000.00, 1200.00, 800.00, 90.00,400.00),
    ('S014', 5000.00, 1000.00, 700.00, 80.00,200.00),
    ('S015', 2000.00, 1200.00, 600.00, 60.00,200.00),
    ('S016', 5000.00, 1000.00, 500.00, 50.00,100.00),
    ('S017', 6000.00, 1200.00, 600.00, 50.00,100.00),
    ('S018', 5000.00, 1000.00, 500.00, 60.00,500.00),
    ('S019', 7000.00, 1200.00, 300.00, 80.00,300.00),
    ('S020', 5000.00, 1000.00, 200.00, 60.00,100.00),
    ('S021', 6000.00, 1300.00, 400.00, 80.00,400.00),
    ('S022', 5000.00, 1000.00, 600.00, 300.00,600.00),
    ('S023', 4000.00, 1400.00, 400.00, 50.00,200.00),
    ('S024', 5000.00, 1000.00, 500.00, 60.00,100.00),
    ('S025', 6000.00, 800.00, 300.00, 200.00,50.00);

INSERT INTO salary 
VALUES
    ('S012', 6000.00, 1200.00, 300.00, 50.00,100.00);


-- 向项目表插入数据
INSERT INTO project (project_id, project_name, start_date, end_date)
VALUES
    ('P001', '智慧家', '2024-01-01', '2024-11-30'),
    ('P002', '药源谱', '2023-03-05', '2024-05-30'),
    ('P003', '生物潜力', '2023-01-01', '2024-11-20'),
    ('P004', '数字令牌', '2023-03-15', '2024-06-30');


-- 向部门表插入数据
INSERT INTO company_department (department_number, department_name, manager_id)
VALUES
    ('D001', '研发部', 'E001'),
    ('D002', '市场部', 'E002'),
    ('D003', '财务部', 'E003');

-- 向职位表添加数据
INSERT INTO department_position 
VALUES
    ('PS001', '研发经理', 'D001'),
    ('PS002', '软件工程师', 'D001'),
    ('PS003', '测试工程师', 'D001'),
    ('PS004', '品牌经理', 'D002'),
    ('PS005', '市场营销专员', 'D002'),
    ('PS006', '财务总监', 'D003'),
    ('PS007', '财务会计', 'D003');
    
    
--     向员工表插入数据
INSERT INTO employee_information 
VALUES
    ('E001', '张三', '男', '5204545682232054', '177859777', '45685545@qq.com', 'D001','PS001','S001','P001'),
    ('E002', '白龙马', '男', '5288845692235456', '17785971', '43335545@qq.com', 'D001','PS002','S002','P001'),
    ('E003', '孙悟空', '男', '5204545682277852', '177859775', '458465545@qq.com', 'D001','PS003','S003','P004'),
    ('E004', '唐三藏', '男', '5204545623336655', '17785885', '45688845@qq.com', 'D001','PS002','S004','P001'),
    ('E005', '白晶晶', '女', '5204548223448855', '17788785', '45688445@qq.com', 'D001','PS002','S005','P004'),
    ('E006', '夯大力', '男', '5204568223231545', '177845785', '45688745@qq.com', 'D001','PS003','S006','P001'),
    ('E007', '宋江', '男', '5204568223206654', '178477785', '454855545@qq.com', 'D001','PS003','S007','P001'),
    ('E008', '张飞', '男', '5254568223207784', '147857785', '78685545@qq.com', 'D001','PS002','S008','P004'),
    ('E009', '猪八戒', '男', '5254568223203321', '18785975', '6545685545@qq.com', 'D001','PS003','S009','P002'),
    ('E010', '武松', '男', '5204568223206642', '178457785', '484585545@qq.com', 'D001','PS003','S010','P001'),
    ('E011', '黑旋风', '男', '5254568223204465', '19965785', '10285545@qq.com', 'D002','PS004','S011','P002'),
    ('E012', '浪里白条', '男', '5204568223200025', '17852785', '448525545@qq.com', 'D002','PS004','S012','P003'),
    ('E013', '薛平贵', '男', '5204568223205474', '171567785', '45699995@qq.com', 'D002','PS005','S013','P003'),
    ('E014', '王宝钏', '女', '5454568223206556', '178629785', '44885545@qq.com', 'D002','PS005','S014','P001'),
    ('E015', '杀生', '男', '5204568223205786', '17785485', '45845545@qq.com', 'D002','PS005','S015','P003'),
    ('E016', '酷狗', '男', '5454568223205454', '17785785', '47785545@qq.com', 'D002','PS005','S016','P001'),
    ('E017', '令狐冲', '男', '5204568223205411', '17777785', '44852545@qq.com', 'D002','PS005','S017','P001'),
    ('E018', '张三丰', '男', '5204568223205422', '17377785', '488955545@qq.com', 'D002','PS005','S018','P004'),
    ('E019', '萧炎', '男', '5204548223205430', '17784785', '49965545@qq.com', 'D002','PS005','S019','P003'),
    ('E020', '唐三', '男', '52045568223205447', '17377785', '47785545@qq.com', 'D002','PS005','S020','P003'),
    ('E021', '石昊', '男', '5204568223209985', '17112985', '77885545@qq.com', 'D002','PS005','S021','P002'),
    ('E022', '小舞', '女', '5454568223203320', '17485785', '99585545@qq.com', 'D003','PS006','S022','P001'),
    ('E023', '奥斯卡', '男', '520454522325546', '17307785', '9568685545@qq.com', 'D003','PS007','S023','P004'),
    ('E024', '朱竹清', '女', '5204545683207785', '17965485', '78855685545@qq.com', 'D003','PS007','S024','P002'),
    ('E025', '柳如烟', '女', '5204545682233021', '17748585', '4888685545@qq.com', 'D003','PS007','S025','P001');



-- 向员工项目关系表中插入数据
INSERT INTO employee_project_relation 
VALUES
    ('E001','P001'),
    ('E011','P002'),
    ('E021','P002'),
    ('E013','P003'),
    ('E014','P004'),
    ('E005','P001'),
    ('E006','P003'),
    ('E003','P002'),
    ('E004','P004'),
    ('E005','P002'),
    ('E003','P003'),
    ('E015','P004'),
    ('E016','P001'),
    ('E017','P002'),
    ('E018','P001'),
    ('E019','P001'),
    ('E020','P003'),
    ('E023','P002'),
    ('E022','P001'),
    ('E023','P003'),
    ('E024','P004'),
    ('E025','P002'),
    ('E003','P001');
    
    
    show tables;

/*
创建表
*/
show tables;
drop table employee_project_relation;
-- 员工和项目关联表

desc employee_project_relation;
CREATE TABLE employee_project_relation (
    employee_number VARCHAR(20),
    project_id VARCHAR(20),
    PRIMARY KEY (employee_number, project_id)
    FOREIGN KEY (employee_number) REFERENCES employee_information(employee_number),
    FOREIGN KEY (project_id) REFERENCES project(project_id);
);

alter table employee_project_relation add FOREIGN KEY (employee_number) REFERENCES employee_information(employee_number);
alter table employee_project_relation add FOREIGN KEY (project_id) REFERENCES project(project_id);

show tables;
 


-- 员工信息表
desc employee_information;
drop table employee_information;
CREATE TABLE employee_information (
    employee_number VARCHAR(20) PRIMARY KEY, -- 员工编号
    employee_name VARCHAR(50) NOT NULL, -- 员工姓名
    gender ENUM('男', '女'), -- 性别
     id_card_number VARCHAR(18) UNIQUE, -- 身份证号
    mobile_phone VARCHAR(11), -- 电话
    email VARCHAR(100),-- 邮箱
    department_number VARCHAR(20),-- 部门编号
    position_number VARCHAR(20),-- 职位编号
    salary_id VARCHAR(20) NOT NULL,-- 工资编号
     project_id VARCHAR(20) ,-- 项目编号
   FOREIGN KEY (salary_id) REFERENCES salary(salary_id),-- 工资表建立外连接
    FOREIGN KEY (department_number) REFERENCES company_department(department_number),-- 部门建立外连接
    FOREIGN KEY (position_number) REFERENCES department_position(position_number)-- 职位建立外连接
);

-- 职位表
desc department_position;
drop table department_position;
CREATE TABLE department_position (
    position_number VARCHAR(20) PRIMARY KEY,-- 职位编号
    position_name VARCHAR(50) NOT NULL,-- 职位名称
    department_number VARCHAR(20) NOT NULL,-- 部门编号
    FOREIGN KEY (department_number) REFERENCES company_department(department_number)
);


-- 部门表
desc company_department;
drop table company_department;
CREATE TABLE company_department (
    department_number VARCHAR(20) PRIMARY KEY,-- 部门编号
    department_name VARCHAR(100) NOT NULL,-- 部门名称
    manager_id VARCHAR(20)-- 负责人编号
);

-- 项目表
desc project;
drop TABLE project;
CREATE TABLE project (
    project_id VARCHAR(20) PRIMARY KEY,-- 项目编号
    project_name VARCHAR(100) NOT NULL,-- 项目名称
    start_date DATE,-- 开始时间
    end_date DATE,  -- 结束时间
    CHECK (start_date <= end_date)
);


-- 工资表
desc salary;
drop table salary;
CREATE TABLE salary (
    salary_id VARCHAR(20) NOT NULL,  -- 工资编号
    basic_salary DECIMAL(10, 2) NOT NULL,  -- 基本工资
    five_insurances_and_one_fund DECIMAL(10, 2),  -- 五险一金
    performance_bonus DECIMAL(10, 2),  -- 绩效奖金
    subsidy DECIMAL(10, 2),  -- 补贴
    overtime_pay DECIMAL(10, 2),  -- 加班费
    PRIMARY KEY (salary_id)
);